<?php
/***************************************************************
*  Copyright notice
*  (c) 2009, jianyuzhu@gmail.com
*  All rights reserved
*  This script is part of the PPEMI project.
***************************************************************/

//
//SHOW TABLES
//		Tables_in_dbname
//		table ...
//SHOW KEYS FROM table
//		Table  	 Non_unique  	 Key_name  	 Seq_in_index  	 Column_name  	 Collation  	 
//		Cardinality  	 Sub_part  	 Packed  	 Null  	 Index_type  	 Comment 
//
//SHOW FIELDS FROM
//		Field  	 Type  	 Null  	 Key  	 Default  	 Extra 
//		id		 int(11)		 PRI	 NULL		 auto_increment
//				 varchar(100), text, tinyint(4), datetime
//
//SHOW TABLE STATUS LIKE 'table'
//		Name  	 Type  	 Row_format  	 Rows  	 Avg_row_length  	 Data_length  	 Max_data_length  	 Index_length  	 
//		Data_free  	 Auto_increment  	 Create_time  	 Update_time  	 Check_time  	 Create_options  	 Comment 

class MySQLDumpBase extends Base {
	var $crlf = "\n";
	var $sep = ";";
	
	//db
	var $db_server = 'localhost';
	var $db_user = '';
	var $db_password = '';
	var $db_name = '';
	
	//dbs
	var $dbs_name = '';
	var $dbs_owner = '';
	var $dbs_path = '';
	
	//constructor
	function MySQLDumpBase() {
		parent::Base();
		
		$this->dbs_path = CONFIG_PATH . CONFIG_DIR_CACHE;
	}
	
	//methods
	function getTables() {
		$tables = $this->adb->getTables();
		
		return $tables;
	}
	
	//
	function getTableSQLData($tableName) {
		$schema = '';
		
		$schema .= 'CREATE TABLE `' . $tableName . '` (' . $this->crlf;
		
		// fields
		$result = $this->adb->query("SHOW FIELDS " . $table);
		while($row = $this->adb->fetch_array($result)) {
			//column
			$schema .= ' ' . $row['field'] . ' ' . $row['type'] . ($row['null'] != 'YES' ? ' NOT NULL' : '');
			
			//default
			if( isset($row['default']) ) {
				$schema .= ' default ' . (is_numeric($row['default']) ? $row['default'] : '\'' . mysql_escape_string($row['default']) . '\'');
			}
			
			//extra
			$schema .= ($row['extra'] != '' ? ' ' . $row['extra'] : '') . ',' . $this->crlf;
		}
		$schema = substr($schema, 0, -strlen($this->crlf) - 1);
		
		// keys
		$indexes = array();
		$result = $this->adb->query("SHOW KEYS FROM " . $table);
		while($row = $this->adb->fetch_array($result)) {
			// is this a primary key, unique index, or regular index?
			$row['Key_name'] = $row['Key_name'] == 'PRIMARY' ? 'PRIMARY KEY' : 
							(empty($row['Non_unique']) ? 'UNIQUE ' : ($row['Comment'] == 'FULLTEXT' ? 'FULLTEXT ' : 'KEY ')) . $row['Key_name'];
			
			// is this the first column in the index?
			if( empty($indexes[$row['Key_name']]) ) $indexes[$row['Key_name']] = array();
			
			// a sub part, like only indexing 15 characters of a varchar.
			if( !empty($row['Sub_part']) ) {
				$indexes[$row['Key_name']][$row['Seq_in_index']] = $row['Column_name'] . '(' . $row['Sub_part'] . ')';
			} else {
				$indexes[$row['Key_name']][$row['Seq_in_index']] = $row['Column_name'];
			}
		}
		
		// now just get the comment and type... (MyISAM, etc.)
		$result = $this->adb->query("SHOW TABLE STATUS LIKE '" . strtr($tableName, array('_' => '\\_', '%' => '\\%')) . "'", $db_connection);
		$row = $this->adb->fetch_assoc($result);
		$this->adb->free_result($result);
		
		// probably MyISAM.... and it might have a comment.
		$schema .= $this->crlf . ') TYPE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) 
					. ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
		
		return $schema;
	}
	
	//
	function getTableSQLDataDetail($tableName) {
		$indexes = array();
		
		//
		$schema = '';
		$schema .= 'CREATE TABLE `' . $tableName . '` (' . $this->crlf;
													   
		$table_fields = array();
		$result_fields = $this->adb->query("SHOW FIELDS FROM " . $tableName);
		while($field = $this->adb->fetch_array($result_fields)) {
			$table_fields[] = $field['Field'];
			
			$schema .= '  ' . $field['Field'] . ' ' . $field['Type'];
			
			if( $field['Null'] != 'YES' ) {
				$schema .= ' NOT NULL';
			}
			if( strlen($field['Default']) > 0 ) {
				$schema .= ' default \'' . $field['Default'] . '\'';
			}
			if( isset($field['Extra']) ) {
				$schema .= ' ' . $field['Extra'];
			}
			
			$schema .= ',' . "\n";
		}
		
		$schema = ereg_replace(",\n$", '', $schema);
		
		// find the keys.
		$result = $this->adb->query("SHOW KEYS FROM `$tableName`");
		while($row = $this->adb->fetch_array($result)){
			// is this a primary key, unique index, or regular index?
			$row['Key_name'] = $row['Key_name'] == 'PRIMARY' ? 'PRIMARY KEY' : 
							(empty($row['Non_unique']) ? 'UNIQUE ' : ($row['Comment'] == 'FULLTEXT' ? 'FULLTEXT ' : 'KEY ')) . $row['Key_name'];
			
			// is this the first column in the index?
			if( empty($indexes[$row['Key_name']]) ) $indexes[$row['Key_name']] = array();
			
			// a sub part, like only indexing 15 characters of a varchar.
			if( !empty($row['Sub_part']) ) {
				$indexes[$row['Key_name']][$row['Seq_in_index']] = $row['Column_name'] . '(' . $row['Sub_part'] . ')';
			} else {
				$indexes[$row['Key_name']][$row['Seq_in_index']] = $row['Column_name'];
			}
		}
		$this->adb->free_result($result);
		
		// build the CREATEs for the keys.
		foreach($indexes as $keyname => $columns) {
			// ensure the columns are in proper order.
			ksort($columns);
			
			$schema .= ',' . $this->crlf . '  ' . $keyname . ' (' . implode($columns, ', ') . ')';
		}
		
		// now just get the comment and type... (MyISAM, etc.)
		$result = $this->adb->query("SHOW TABLE STATUS LIKE '" . strtr($tableName, array('_' => '\\_', '%' => '\\%')) . "'", $db_connection);
		$row = $this->adb->fetch_assoc($result);
		$this->adb->free_result($result);
		
		// probably MyISAM.... and it might have a comment.
		$schema .= $this->crlf . ') TYPE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) 
					. ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
		
		return $schema;
	}
	
	//backup
	function backup() {
		set_time_limit(0);
		
		//
		$backup_file = 'db_' . $this->db_name . '-' . date('YmdHis') . '.sql';
		
		//
		$fp = fopen($this->dbs_path . $backup_file, 'w');
		
		//
		$schema = '# PPEMI Database Backup/Restore Solutions' . "\n" .
				'# http://www.ppemi.com' . "\n" .
				'#' . "\n" .
				'# Database Backup For ' . $this->dbs_name . "\n" .
				'# Copyright (c) ' . date('Y') . ' ' . $this->dbs_owner . "\n" .
				'#' . "\n" .
				'# Database: ' . $this->db_name . "\n" .
				'# Database Server: ' . $this->db_server . "\n" .
				'#' . "\n" .
				'# Backup Date: ' . date('%Y-%m-%d %H:%M:%S') . "\n\n";
		fputs($fp, $schema);
		
		//
		$result_tables = $this->adb->query("SHOW TABLES");
		while($tables = $this->adb->fetch_array($result_tables)) {
			list(,$table) = each($tables);
			
			$schema = 'DROP TABLE IF EXISTS ' . $table . ';' . "\n" 
					. 'CREATE TABLE ' . $table . ' (' . "\n";
			
			$table_fields = array();
			$result_fields = $this->adb->query("SHOW FIELDS FROM " . $table);
			while($field = $this->adb->fetch_array($result_fields)) {
				$table_fields[] = $field['Field'];
				
				$schema .= '  ' . $field['Field'] . ' ' . $field['Type'];
				
				if( $field['Null'] != 'YES' ) {
					$schema .= ' NOT NULL';
				}
				if( strlen($field['Default']) > 0 ) {
					$schema .= ' default \'' . $field['Default'] . '\'';
				}
				if( isset($field['Extra']) ) {
					$schema .= ' ' . $field['Extra'];
				}
				
				$schema .= ',' . "\n";
			}
			
			$schema = ereg_replace(",\n$", '', $schema);
			
			// add the keys
			$index = array();
			$result_keys = $this->adb->query("SHOW KEYS FROM " . $table);
			while($key = $this->adb->fetch_array($result_keys)) {
				$kname = $key['Key_name'];
				
				if( !isset($index[$kname]) ) {
					$index[$kname] = array(
								'unique' => !$key['Non_unique'], 
								'fulltext' => ($key['Index_type'] == 'FULLTEXT' ? '1' : '0'), 
								'columns' => array(),
									);
				}
				
				$index[$kname]['columns'][] = $key['Column_name'];
			}
			
			while(list($kname, $info) = each($index)) {
				$schema .= ',' . "\n";
				
				$columns = implode($info['columns'], ', ');
				
				if( $kname == 'PRIMARY' ) {
					$schema .= '  PRIMARY KEY (' . $columns . ')';
				} elseif( $info['fulltext'] == '1' ) {
					$schema .= '  FULLTEXT ' . $kname . ' (' . $columns . ')';
				} elseif( $info['unique'] ) {
					$schema .= '  UNIQUE ' . $kname . ' (' . $columns . ')';
				} else {
					$schema .= '  KEY ' . $kname . ' (' . $columns . ')';
				}
			}
			
			$schema .= "\n" . ');' . "\n\n";
			fputs($fp, $schema);
			
			// dump the data
			$result_data = $this->adb->query("SELECT " . implode(',', $table_fields) . " FROM " . $table);
			while($rows = $this->adb->fetch_array($result_data)) {
				$schema = 'INSERT INTO ' . $table . ' (' . implode(', ', $table_fields) . ') VALUES (';
				
				reset($table_fields);
				while(list(,$i) = each($table_fields)) {
					if( !isset($rows[$i]) ) {
						$schema .= 'NULL, ';
					} elseif( util_not_null($rows[$i]) ) {
						$row = addslashes($rows[$i]);
						$row = ereg_replace("\n#", "\n".'\#', $row);
						
						$schema .= '\'' . $row . '\', ';
					} else {
						$schema .= '\'\', ';
					}
				}
				
				$schema = ereg_replace(', $', '', $schema) . ');' . "\n";
				fputs($fp, $schema);
			
			}
		}
		
		//
		fclose($fp);
		
		//
		if( isset($this->download) && ($this->download == 'yes') ) {
			switch($this->compress) {
				case 'gzip':
					exec($this->local_exe_gzip . ' ' . $this->dbs_path . $backup_file);
					$backup_file .= '.gz';
					break;
				case 'zip':
					exec($this->local_exe_zip . ' -j ' . $this->dbs_path . $backup_file . '.zip ' . $this->dbs_path . $backup_file);
					unlink($this->dbs_path . $backup_file);
					$backup_file .= '.zip';
			}
			header('Content-type: application/x-octet-stream');
			header('Content-disposition: attachment; filename=' . $backup_file);
			
			readfile($this->dbs_path . $backup_file);
			unlink($this->dbs_path . $backup_file);
			
			exit;
		} else {
			switch($this->compress ) {
				case 'gzip':
					exec($this->local_exe_gzip . ' ' . $this->dbs_path . $backup_file);
					break;
				case 'zip':
					exec($this->local_exe_zip . ' -j ' . $this->dbs_path . $backup_file . '.zip ' . $this->dbs_path . $backup_file);
					unlink($this->dbs_path . $backup_file);
			}
			
			//
			$messagestackObj->add_session(_lang(':success_database_saved'), 'success');
		}
	}
	
	//restore
	function restore() {
		set_time_limit(0);
		
		//
		$read_from = $this->restore_filename;
		if( file_exists($this->dbs_path . $read_from) ) {
			$restore_file = $this->dbs_path . $read_from;
			$extension = substr($read_from, -3);
			
			if( ($extension == 'sql') || ($extension == '.gz') || ($extension == 'zip') ) {
				switch($extension) {
					case 'sql':
						$restore_from = $restore_file;
						$remove_raw = false;
						break;
					case '.gz':
						$restore_from = substr($restore_file, 0, -3);
						exec($this->local_exe_gunzip . ' ' . $restore_file . ' -c > ' . $restore_from);
						$remove_raw = true;
						break;
					case 'zip':
						$restore_from = substr($restore_file, 0, -4);
						exec($this->local_exe_unzip . ' ' . $restore_file . ' -d ' . $this->dbs_path);
						$remove_raw = true;
				}
				
				if( isset($restore_from) && file_exists($restore_from) && (filesize($restore_from) > 15000) ) {
					$fd = fopen($restore_from, 'rb');
					$restore_query = fread($fd, filesize($restore_from));
					fclose($fd);
				}
			}
		}
		
		//
		if( isset($restore_query) ) {
			$sql_array = array();
			$drop_table_names = array();
			
			$sql_length = strlen($restore_query);
			$pos = strpos($restore_query, ';');
			
			for($i=$pos; $i<$sql_length; $i++) {
				if( $restore_query[0] == '#') {
					$restore_query = ltrim(substr($restore_query, strpos($restore_query, "\n")));
					$sql_length = strlen($restore_query);
					$i = strpos($restore_query, ';')-1;
					continue;
				}
				if( $restore_query[($i+1)] == "\n") {
					for($j=($i+2); $j<$sql_length; $j++) {
						if( trim($restore_query[$j]) != '' ) {
							$next = substr($restore_query, $j, 6);
							if( $next[0] == '#') {
								// find out where the break position is so we can remove this line (#comment line)
								for($k=$j; $k<$sql_length; $k++) {
									if( $restore_query[$k] == "\n" ) {
										break;
									}
								}
								$query = substr($restore_query, 0, $i+1);
								$restore_query = substr($restore_query, $k);
								
								// join the query before the comment appeared, with the rest of the dump
								$restore_query = $query . $restore_query;
								$sql_length = strlen($restore_query);
								$i = strpos($restore_query, ';')-1;
								continue 2;
							}
							break;
						}
					}
					// get the last insert query
					if( $next == '' ) {
						$next = 'insert';
					}
					if( (eregi('create', $next)) || (eregi('insert', $next)) || (eregi('drop t', $next)) ) {
						$next = '';
						$sql_array[] = substr($restore_query, 0, $i);
						$restore_query = ltrim(substr($restore_query, $i+1));
						$sql_length = strlen($restore_query);
						$i = strpos($restore_query, ';')-1;
						
						if (eregi('^create*', $query)) {
							$table_name = trim(substr($query, stripos($query, 'table ')+6));
							$table_name = substr($table_name, 0, strpos($table_name, ' '));
							$drop_table_names[] = $table_name;
						}
					}
				}
			}
			
			//
			$this->adb->query("DRIP TABLE IF EXISTS " . implode(', ', $drop_table_names));
			
			for($i=0, $n=sizeof($sql_array); $i<$n; $i++) {
				$this->adb->query($sql_array[$i]);
			}
			
			//
			$this->restore_status = 1;
			
			//
			if( isset($remove_raw) && ($remove_raw == true) ) {
				unlink($restore_from);
			}
			
			//
			$messagestackObj->add_session(_lang(':success_database_restored', 'success'));
		}
	}
}
//
?>